﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class WaterReserveDAL
    {
        public static string conn = ConfigurationSettings.AppSettings["con"].ToString();
        public int AddWaterReserveRecords(string rfkh, string stime,string etime,string wz,string code,string state,string orderId)
        {
            try
            {
                SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter("@rfkh",rfkh),
                new SqlParameter("@stime",stime),
                new SqlParameter("@etime",etime),
                new SqlParameter("@wz",wz),
                new SqlParameter("@code",code),
                new SqlParameter("@orderId",orderId),
                new SqlParameter("@state",state)

            };

                string sqlstr = @"insert into dbo.bookwater(ryid,rfkh,stime,etime,wz,code,state,orderId,timespan,booktime) 
                                                values( (select ryid from ryxx where  (Tel = @rfkh or dh = @rfkh or rybh = @rfkh or sfz = @rfkh or rfkh = @rfkh)), @rfkh,@stime, @etime, @wz, @code, @state,@orderId,30,getdate())
                                                ";
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlstr, sp);
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("添加预约水控AddWaterReserveRecords异常信息为=" + ex.Message);
                return -1;
            }

        }

   

        public int AddWaterReserveOffRecords(string rfkh, string stime, string etime, string jh, string code, string state, string orderId)
        {
            try
            {
                SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter("@rfkh",rfkh),
                new SqlParameter("@stime",stime),
                new SqlParameter("@etime",etime),
                new SqlParameter("@jh",jh),
                new SqlParameter("@code",code),
                new SqlParameter("@orderId",orderId),
                new SqlParameter("@state",state)

            };

                string sqlstr = @"insert into dbo.BookWaterDownList(ryid,rfkh,stime,etime,jh,code,OfflineStatus,sn,booktime) 
                                                values( (select ryid from ryxx where  (Tel = @rfkh or dh = @rfkh or rybh = @rfkh or sfz = @rfkh or rfkh = @rfkh)), @rfkh,@stime, @etime, @jh, @code, @state,(select sn from jhsz where jh=@jh),getdate())
                                                ";
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlstr, sp);
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("添加预约水控离线记录 异常信息为=" + ex.Message);
                return -1;
            }

        }

        public DataSet GetWaterReserveRecords(string openids, string startTime, string endTime)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@openid",openids),
                new SqlParameter("@startTime",startTime),
                new SqlParameter("@endTime",endTime)
            };
            try
            {
                string sqlstr = @"select ryid,jh,rfkh,code,stime,etime,wz,case when [state]=1 then '成功'  when [state]=2 then '已取消' when [state]=3 then '出水中' 
                                            when [state]=4 then '停止' when [state]=5 then '违约' when [state]=6 then '结束' else ''end [state],orderid timespan,sn,booktime 
                                            from BookWater where ryid=(select ryid from IdBind where openid=@openid ) and 
                                            booktime between @startTime  and @endTime  order by  stime desc";
                DataSet ds = SqlHelper.ExecuteDataSet(conn, CommandType.Text, sqlstr, sp);
                return ds;

            }
            catch (Exception ex)
            {
                ApplicationLog.Write("获取水控预约码异常信息为="+ex.Message);
                throw;
            }
        }





        public DataTable getJh(string wz)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@wz",wz)
            };


            try
            {
                string sqlstr = "select jh from jhsz where wz=@wz";
                DataTable dt = SqlHelper.ExecuteDataSet(conn, CommandType.Text, sqlstr, sp).Tables[0];
                return dt;

            }
            catch (Exception ex)
            {
                ApplicationLog.Write("获取预约位置的所有机号异常信息为=" + ex.Message);
                throw;
            }


        }

        public int CancelWaterReserveRecords(string rfkh, string endTime, string wz, string reservedCode)
        {
            try
            {
                SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter("@rfkh",rfkh),
                new SqlParameter("@endTime",endTime),
                new SqlParameter("@wz",wz),
                new SqlParameter("@reservedCode",reservedCode)

            };

                string sqlstr = @"update BookWater set [state]=2 where wz=@wz and code=@reservedCode and rfkh=@rfkh 
                                             update BookWaterDownList set [OfflineStatus]=2 where code=@reservedCode and rfkh=@rfkh ";
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlstr, sp);
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("取消预约水控离线记录 异常信息为=" + ex.Message);
                return -1;
            }
        }



        public DataTable isExistReservedCode(string wz,string reservedCode)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@wz",wz),
                new SqlParameter("@reservedCode",reservedCode)
            };


            try
            {
                string sqlstr = "select code from bookwater where wz=@wz and code=@reservedCode";
                DataTable dt = SqlHelper.ExecuteDataSet(conn, CommandType.Text, sqlstr, sp).Tables[0];
                return dt;

            }
            catch (Exception ex)
            {
                ApplicationLog.Write("判断预约号在同一个区域是否重复异常信息为=" + ex.Message+"，区域位置="+wz);
                throw;
            }

        }



    }

}
